/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.projecao.laboratorio.dao;

import br.projecao.laboratorio.negocio.Aluno;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Moises.Moura
 */
public class AlunoDAO extends DAO {

    private String insertSql = "INSERT INTO lab_aluno (matricula,email,senha,nome,telefone,celular,perfil) VALUES(?,?,?,?,?,?,?); ";

    public void incluirAluno(Aluno aluno) throws SQLException {
        try {
            abreConexao();
            PreparedStatement pstm = conexao.prepareStatement(insertSql.toString());
            pstm.setInt(1, aluno.getMatricula());
            pstm.setString(2, aluno.getEmail());
            pstm.setString(3, aluno.getSenha());
            pstm.setString(4, aluno.getNome());
            pstm.setString(5, aluno.getTelefone());
            pstm.setString(6, aluno.getCelular());
            pstm.setInt(7, 0);
            pstm.execute();
        } catch (SQLException e) {
            throw e;
        } finally {
            fechaConexao();
        }
    }

    public Aluno consultaPorEmail(Aluno aluno) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT matricula,email,senha,nome,telefone,celular,perfil ");
        sql.append(" FROM lab_aluno ");
        sql.append(" WHERE email = ?");
        try {
            abreConexao();
            PreparedStatement pstm = conexao.prepareStatement(sql.toString());
            pstm.setString(1, aluno.getEmail());
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                aluno = new Aluno();
                aluno.setMatricula(rs.getInt("matricula"));
                aluno.setEmail(rs.getString("email"));
                aluno.setSenha(rs.getString("senha"));
                aluno.setNome(rs.getString("nome"));
                aluno.setTelefone(rs.getString("telefone"));
                aluno.setCelular(rs.getString("celular"));
                aluno.setPerfil(rs.getInt("perfil"));
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            fechaConexao();
        }
        return aluno;
    }

    public ArrayList<Aluno> consultar(Aluno aluno) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT matricula,email,senha,nome,telefone,celular,perfil ");
        sql.append(" FROM lab_aluno ");
        sql.append(" WHERE nome like ? or matricula = ?");
        ArrayList<Aluno> alunos = new ArrayList<Aluno>();
        try {
            abreConexao();
            PreparedStatement pstm = conexao.prepareStatement(sql.toString());
            if (aluno.getMatricula() != null && aluno.getMatricula() != 0) {
                pstm.setString(1, aluno.getNome());
            } else {
                pstm.setString(1, "%" + aluno.getNome() + "%");
            }
            pstm.setInt(2, aluno.getMatricula());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                aluno = new Aluno();
                aluno.setMatricula(rs.getInt("matricula"));
                aluno.setEmail(rs.getString("email"));
                aluno.setSenha(rs.getString("senha"));
                aluno.setNome(rs.getString("nome"));
                aluno.setTelefone(rs.getString("telefone"));
                aluno.setCelular(rs.getString("celular"));
                aluno.setPerfil(rs.getInt("perfil"));
                alunos.add(aluno);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            fechaConexao();
        }
        return alunos;
    }

    public Aluno consultarPorMatricula(Aluno aluno) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT matricula,email,senha,nome,telefone,celular,perfil ");
        sql.append(" FROM lab_aluno ");
        sql.append(" WHERE matricula = ?");
        try {
            abreConexao();
            PreparedStatement pstm = conexao.prepareStatement(sql.toString());
            pstm.setInt(1, aluno.getMatricula());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                aluno = new Aluno();
                aluno.setMatricula(rs.getInt("matricula"));
                aluno.setEmail(rs.getString("email"));
                aluno.setSenha(rs.getString("senha"));
                aluno.setNome(rs.getString("nome"));
                aluno.setTelefone(rs.getString("telefone"));
                aluno.setCelular(rs.getString("celular"));
                aluno.setPerfil(rs.getInt("perfil"));
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            fechaConexao();
        }
        return aluno;
    }

    public void alterar(Aluno aluno) throws SQLException {
        StringBuilder sql = new StringBuilder("UPDATE lab_aluno set ");
        sql.append(" email = ?, ");
        sql.append(" senha = ?, ");
        sql.append(" nome = ?, ");
        sql.append(" telefone = ?, ");
        sql.append(" celular = ? ");
        sql.append(" WHERE ");
        sql.append(" matricula = ? ");
        try {
            abreConexao();
            PreparedStatement pstm = conexao.prepareStatement(sql.toString());
            pstm.setString(1, aluno.getEmail());
            pstm.setString(2, aluno.getSenha());
            pstm.setString(3, aluno.getNome());
            pstm.setString(4, aluno.getTelefone());
            pstm.setString(5, aluno.getCelular());
            pstm.setInt(6, aluno.getMatricula());
            pstm.execute();
        } catch (SQLException e) {
            throw e;
        } finally {
            fechaConexao();
        }
    }

    public void excluir(Aluno aluno) throws SQLException {
        StringBuilder sql = new StringBuilder("DELETE FROM lab_aluno ");
        sql.append(" WHERE ");
        sql.append(" matricula = ? ");
        try {
            abreConexao();
            PreparedStatement pstm = conexao.prepareStatement(sql.toString());
            pstm.setInt(1, aluno.getMatricula());
            pstm.execute();
        } catch (SQLException e) {
            throw e;
        } finally {
            fechaConexao();
        }
    }

    public ArrayList<Aluno> consultar() throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT matricula,email,senha,nome,telefone,celular,perfil ");
        sql.append(" FROM lab_aluno order by nome");
        ArrayList<Aluno> alunos = new ArrayList<Aluno>();
        try {
            abreConexao();
            PreparedStatement pstm = conexao.prepareStatement(sql.toString());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                Aluno aluno = new Aluno();
                aluno.setMatricula(rs.getInt("matricula"));
                aluno.setEmail(rs.getString("email"));
                aluno.setSenha(rs.getString("senha"));
                aluno.setNome(rs.getString("nome"));
                aluno.setTelefone(rs.getString("telefone"));
                aluno.setCelular(rs.getString("celular"));
                aluno.setPerfil(rs.getInt("perfil"));
                alunos.add(aluno);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            fechaConexao();
        }
        return alunos;
    }
}
